<?php
require_once($_SERVER['DOCUMENT_ROOT']."/defaultconf.mex");
require_once($GLOBALS['PAD']."Aplications/ReportModels/report.vars.php");
MYTCONN();
	$viewtype = $_POST['viewtype'];
	$QrCountry = '';
	$RsProfit = '';
	if(!isset($_POST['ConsultDate']))	{ $_POST['ConsultDate'] = date("Y-m-d");}
	if(!isset($_POST['Origens'])) 		{ $_POST['Origens'] 	= 1; }

	if($viewtype==1){
		$Tini = $_POST['ConsultDate'];
		$Tend = $_POST['ConsultDate'];
		$viewloop=2;
	}elseif($viewtype==2){
		$Tini = $_POST['Tini'];
		$Tend = $_POST['Tend'];
		$viewloop=1;
	}
	if($viewtype==1){$viewloop=2;}elseif($viewtype==2){$viewloop=1;}
	if(isset($_POST['Countries'])){
			if($_POST['Countries']>0){
			$QrCountry = " AND pai_Profit = {$_POST['Countries']}";
			} else {
			$_POST['Countries']=0;
			}
			$RsProfit = " AND RsProfit = {$_POST['Countries']}";

	}
	$RPSTR = '';
	include($GLOBALS['OBJ'].'obj.mathgrids.mex');
	$NoResults = '<tr bgcolor="#F2F2F2"><td colspan="14">NÃo existem resultados para apresentação.</td></tr>';

	$FinantialResult = 0;

	$head = '<tr style="background-color:#C7D3D8;height:18px;text-align:center">
	<td NOWRAP width="17%"></td>
	<td NOWRAP width="7%">#</td>
	<td NOWRAP width="9%" colspan="';
	if ($_POST['Countries'] == 2) {
	$head .= '4';
	} else {
	$head .= '3';
	}
	$head .= '">Valores</td>
	<td NOWRAP width="10%" colspan="3">Lucro Bruto</td>
	<td NOWRAP width="10%" colspan="3">Por remessa</td>
	<td NOWRAP width="10%">Por funcionário</td>
	<td NOWRAP width="10%">Novos</td>';
	if($_POST['Countries']==0){
	$head .= '<td NOWRAP width="8%" colspan="2">Lucro Liquido</td>';
	} else {
	$head .= '<td NOWRAP width="8%" colspan="3">Meta</td>';
	}
	$head .= '</tr>';

	### Sub-title
	$head .= '<tr style="background-color:#D7E3E8;height:18px;text-align:center">
	<td width="12%">Lojas</td>
	<td width="7%">Remessas</td>
	<td width="7%">Lote</td>';
	if ($_POST['Countries'] == 2) {
	$head .= '<td width="7%">Co.Money</td>
	<td width="7%">Co.Corr</td>
	<td width="7%">Imp.Selo</td>';
	} else {
	$head .= '<td width="7%">Taxas</td>';
	$head .= '<td width="7%">Promocoes</td>';
	}
	$head .= '<td width="7%">Lote</td>
	<td width="7%">Total</td>
	<td width="7%">%</td>';
	if ($_POST['Countries'] == 2) {
	$head .= '<td width="7%">ComissÃo</td>';
	} else {
	$head .= '<td width="7%">Taxa</td>';
	}
	$head .= '<td width="7%">Lucro</td>
	<td width="7%">Valor Médio</td>
	<td width="7%">Remessas</td>
	<td width="7%">Clientes</td>';
	if($_POST['Countries']==0){
	$head .= '<td width="7%">Despesa</td>
	<td width="7%">Liquido</td>';
	} else {
	$head .= '<td NOWRAP width="6%">Valor</td>
	<td NOWRAP width="6%">Em Falta</td>
	<td NOWRAP width="6%">% Cumprido</td>';
	}
	$head .= '</tr>';

	$RPSTR .= $tablehead;

	for($l=1;$l<=$viewloop;$l++) {

	if($l==1){

	$ConsultDay = substr($_POST['ConsultDate'],8,2);
	$ConsultMonth = substr($_POST['ConsultDate'],5,2);
	$ConsultYear = substr($_POST['ConsultDate'],0,4);
	$RateField = $_POST['RelRate'];

	if ($_POST['Origens'] == 1) {
	$QrOrigens = 'FraOrigen0';
	$QrOrNAME = 'main_offices.name';
	} elseif($_POST['Origens'] == 2) {
	$QrOrigens = 'FraOrigen1';
	$QrOrNAME = 'OriNombre';
	} elseif($_POST['Origens'] == 3) {
	$QrOrigens = 'OriTipo';
	$QrOrNAME = 'OriTipo';
	} elseif($_POST['Origens'] == 4) {
	$QrOrigens = 'dom_Region';
	$QrOrNAME = 'dom_Region';
	}
	$currency = '';
	if($viewtype==1){
	$RPSTR .= '<tr><td colspan="20"><div style="font-weight:bold;text-align:center;border:1px solid #CCC;margin:2px;padding:3px;background-color:#F2F2F2">Resumo diário de lucro por lojas do dia '.$ConsultDay.'-'.$ConsultMonth.'-'.$ConsultYear.'</div></td></tr>';
	}elseif($viewtype==2){
	$RPSTR .= '<tr><td colspan="20"><div style="font-weight:bold;text-align:center;border:1px solid #CCC;margin:2px;padding:3px;background-color:#F2F2F2">Resumo por lojas no periodo entre '.$Tini.' e '.$Tend.'</div></td></tr>';
	}
	$RPSTR .= $head;
	$TotalSumWorkers = 0;

	$QResults = "SELECT
	IF({$QrOrigens}=FraOrigen0
		,IF(FraOrigen0=822 AND FraFechaIngreso<'2010-03-16','8220',
				IF(FraOrigen0=814 AND OriTipo = 1 AND FraFechaIngreso<'2010-03-16','8140',FraOrigen0)),
	IF({$QrOrigens}=dom_Region,
		 IF(FraOrigen0 = 822 AND FraFechaIngreso<'2010-03-16','Grande Lisboa',
				IF(FraOrigen0=814 AND OriTipo = 1 AND FraFechaIngreso<'2010-03-16','Call Center Portugal',{$QrOrigens})),
	{$QrOrigens})) AS OrderOffice,
	SUM(FraEnviado+FraProm) AS OUTAmount,
	SUM(FraARecibir*CorrRate) AS INAmount,
	SUM((FraARecibir*CorrRate)/{$RateField}) AS FinResult,
	SUM(FraCargo) AS Tax,
	SUM(FraProm) AS Prom,
	SUM(IF(FraStatus IN (10,21),-1,1)) AS Counter,
	COUNT(DISTINCT CliNumero) AS NewClients,
	main_offices.ref AS OfficeID,
	IF({$QrOrNAME}=dom_Region,
		 IF(FraOrigen0 = 822 AND FraFechaIngreso<'2010-03-16','Grande Lisboa',
				IF(FraOrigen0=814 AND OriTipo = 1 AND FraFechaIngreso<'2010-03-16','Call Center Portugal',{$QrOrNAME})),
	{$QrOrNAME}) AS OfficeNAME,
	SUM(DISTINCT RsAmount+RsID)-SUM(DISTINCT RsID) AS ValAmount,
	IF(StaffMembers.Sum>1,StaffMembers.Sum,1) AS Members
	FROM ordenespago
	INNER JOIN ordenesfinanceiro ON Of_Orden = Op_Orden AND {$RateField} > 0
	INNER JOIN facturas ON FraOrdenPago = Op_Orden AND FraID > 400000 AND FraFecha BETWEEN '{$Tini} 00:00:00' AND '{$Tend} 23:59:59' AND FraStatus IN (7,8,9,10,11,21)
	INNER JOIN beneficiarios ON BnfNumero = FraBnfNumero
	INNER JOIN hd_Paises ON pai_Codelite = BnfPais {$QrCountry}
	LEFT OUTER JOIN clientes ON FraCliNumero = CliNumero AND CliFechaAlta BETWEEN '{$Tini} 00:00:00' AND '{$Tend} 23:59:59'
	LEFT OUTER JOIN origenes ON OriNumero = FraOrigen1
	LEFT OUTER JOIN dominios ON OriDominio = dom_ID
	RIGHT OUTER JOIN {$__DBD}.main_offices ON office_number = IF(FraOrigen0=822 AND FraFechaIngreso<'2010-03-16','8220',IF(FraOrigen0=814 AND OriTipo = 1 AND FraFechaIngreso<'2010-03-16',IF({$QrOrigens}=dom_Region,822,'8140'),FraOrigen0))
	LEFT OUTER JOIN {$__DBD}.finantial_results ON RsType = IF({$_POST['Countries']}>0,1,2) {$RsProfit} AND MONTH(RsDate) = MONTH('{$Tini}') AND YEAR(RsDate) = YEAR('{$Tini}') AND RsOri = IF(FraOrigen0=822 AND FraFechaIngreso<'2010-03-16','8220',IF(FraOrigen0=814 AND OriTipo = 1 AND FraFechaIngreso<'2010-03-16',IF({$QrOrigens}=dom_Region,822,'8140'),FraOrigen0))
	LEFT OUTER JOIN (SELECT RefOffice, Sum FROM (SELECT RefOffice, Sum FROM {$__DBD}.staff ORDER BY Time DESC) AS StaffList GROUP BY RefOffice) AS StaffMembers ON RefOffice = main_offices.ref
	WHERE Type = 1
	GROUP BY main_offices.ref
	ORDER BY main_offices.ref";

	} elseif($l==2) {

	$Interv = $GLOBALS['MYT']->db_array("SELECT
	MAX(DAY(Op_Fecha)) AS End
	FROM ordenespago
	WHERE (MONTH(Op_Fecha) = '{$ConsultMonth}' AND YEAR(Op_Fecha) = '{$ConsultYear}')

		GROUP BY MONTH(Op_Fecha) ");

	$Long = mktime(10,0,0,$ConsultMonth,15,$ConsultYear);
	$Mes = date("M, Y",$Long);
	$RPSTR .= '<tr><td colspan="20">';
	$RPSTR .= '<div style="font-weight:bold;text-align:center;border:1px solid #CCC;margin:2px;padding:3px;background-color:#F2F2F2">Resumo Mensal de lucro por lojas do mês '.$Mes;
	if (isset($Interv[1])) {
	$RPSTR .= ' (Até dia '.$Interv[1]['End'].')';
	}
	$RPSTR .= '</div></td></tr>';
	$RPSTR .= $head;
	$TotalSumWorkers = 0;

	$QResults = "SELECT
	IF({$QrOrigens}=FraOrigen0
		,IF(FraOrigen0=822 AND FraFechaIngreso<'2010-03-16','8220',
				IF(FraOrigen0=814 AND OriTipo = 1 AND FraFechaIngreso<'2010-03-16','8140',FraOrigen0)),
	IF({$QrOrigens}=dom_Region,
		 IF(FraOrigen0 = 822 AND FraFechaIngreso<'2010-03-16','Grande Lisboa',
				IF(FraOrigen0=814 AND OriTipo = 1 AND FraFechaIngreso<'2010-03-16','Call Center Portugal',{$QrOrigens})),
	{$QrOrigens})) AS OrderOffice,
	SUM(FraEnviado+FraProm) AS OUTAmount,
	SUM(FraARecibir*CorrRate) AS INAmount,
	SUM((FraARecibir*CorrRate)/{$RateField}) AS FinResult,
	SUM(FraCargo) AS Tax,
	SUM(FraProm) AS Prom,
	SUM(IF(FraStatus IN (10,21),-1,1)) AS Counter,
	COUNT(DISTINCT CliNumero) AS NewClients,
	main_offices.ref AS OfficeID,
	IF({$QrOrNAME}=dom_Region,
		 IF(FraOrigen0 = 822 AND FraFechaIngreso<'2010-03-16','Grande Lisboa',
				IF(FraOrigen0=814 AND OriTipo = 1 AND FraFechaIngreso<'2010-03-16','Call Center Portugal',{$QrOrNAME})),
	{$QrOrNAME}) AS OfficeNAME,
	SUM(DISTINCT RsAmount+RsID)-SUM(DISTINCT RsID) AS ValAmount,
	IF(StaffMembers.Sum>1,StaffMembers.Sum,1) AS Members
	FROM ordenespago
	INNER JOIN ordenesfinanceiro ON Of_Orden = Op_Orden AND {$RateField} > 0
	INNER JOIN facturas ON FraOrdenPago = Op_Orden AND  FraID > 400000 AND (MONTH(FraFecha) = '{$ConsultMonth}' AND YEAR(FraFecha) = '{$ConsultYear}') AND FraStatus IN (7,8,9,10,11,21)
	INNER JOIN beneficiarios ON BnfNumero = FraBnfNumero
	INNER JOIN hd_Paises ON pai_Codelite = BnfPais {$QrCountry}
	LEFT OUTER JOIN clientes ON FraCliNumero = CliNumero AND (MONTH(CliFechaAlta) = '{$ConsultMonth}' AND YEAR(CliFechaAlta) = '{$ConsultYear}')
	LEFT OUTER JOIN origenes ON OriNumero = FraOrigen1
	LEFT OUTER JOIN dominios ON OriDominio = dom_ID
	RIGHT OUTER JOIN {$__DBD}.main_offices ON office_number = IF(FraOrigen0=822 AND FraFechaIngreso<'2010-03-16','8220',IF(FraOrigen0=814 AND OriTipo = 1 AND FraFechaIngreso<'2010-03-16',IF({$QrOrigens}=dom_Region,822,'8140'),FraOrigen0))
	LEFT OUTER JOIN {$__DBD}.finantial_results ON RsType = IF({$_POST['Countries']}>0,1,2) {$RsProfit} AND YEAR(RsDate) = '{$ConsultYear}' AND MONTH(RsDate) = '{$ConsultMonth}' AND RsOri = IF(FraOrigen0=822 AND FraFechaIngreso<'2010-03-16','8220',IF(FraOrigen0=814 AND OriTipo = 1 AND FraFechaIngreso<'2010-03-16',IF({$QrOrigens}=dom_Region,822,'8140'),FraOrigen0))
	LEFT OUTER JOIN (SELECT RefOffice, Sum FROM (SELECT RefOffice, Sum FROM {$__DBD}.staff ORDER BY Time DESC) AS StaffList GROUP BY RefOffice) AS StaffMembers ON RefOffice = main_offices.ref
	WHERE Type = 1
	GROUP BY main_offices.ref
	ORDER BY main_offices.ref";
	}
	$GET = $GLOBALS['MYT']->db_array($QResults);
	$CGET = $GET[0]+1;
if (!isset($GET[1])) {
	$RPSTR .= $NoResults;
} else {
	for ($h = 1; $h <= $CGET; $h++) {
		if(!isset($GET[$h]['ValAmount'])){$GET[$h]['ValAmount']=0;}
		if(FieldResult($GET,$h,'ValAmount')>0){$PercentGoal = FieldResult($GET,$h,'OUTAmount')/FieldResult($GET,$h,'ValAmount')*100;}else{$PercentGoal = '0';}

		if ($_POST['Countries'] == 2) {
		  $REL = FieldResult($GET,$h,'OUTAmount')/100;
		  $GET[$h]['imp'] = $REL*0.12;
		  $GET[$h]['COMM'] = FieldResult($GET,$h,'Tax')-FieldResult($GET,$h,'imp');
		  $GET[$h]['COMM1'] = ((FieldResult($GET,$h,'COMM')/3)*1);
		  $GET[$h]['Tax'] = FieldResult($GET,$h,'COMM1');
		  $GET[$h]['COMM2'] = ((FieldResult($GET,$h,'COMM')/3)*2);
		  }

	$RateIN = FieldResult($GET,$h,'INAmount')/FieldResult($GET,$h,'OUTAmount');
	$RateOUT = FieldResult($GET,$h,'INAmount')/FieldResult($GET,$h,'FinResult');
	$ProfitAmount = FieldResult($GET,$h,'OUTAmount')-FieldResult($GET,$h,'FinResult');
	$Profit = ($ProfitAmount*100)/FieldResult($GET,$h,'OUTAmount');

	$RPSTR .= '<tr bgcolor="';
	if($h < $CGET){$RPSTR .= gridlines($h);}else{$RPSTR .= '#CCCCCC';}
	$RPSTR .= '" height="18">
	<td NOWRAP style="padding:1px;border-bottom:1px solid #BBB;">';
	if ($h != $CGET) {
	$RPSTR .= $GET[$h]['OfficeNAME'];
	} else {
  	$RPSTR .= 'Totais: ';
	}
    $RPSTR .= '</td>
	<td align="right" style="padding-left:10px;padding-right:2px;border-bottom:1px solid #BBB;border-left:1px solid #777;">'.FieldResult($GET,$h,'Counter').'</td>
	<td align="right" style="padding-left:10px;padding-right:2px;border-bottom:1px solid #BBB;border-left:1px solid #777;">'.number_format(FieldResult($GET,$h,'OUTAmount'),2,',','.').$currency.'</td>
	<td align="right" style="padding-left:10px;padding-right:2px;border-bottom:1px solid #BBB;border-left:1px solid #CCC;">';

	if ($_POST['Countries'] == 2) {
	$ProfitAmount = $ProfitAmount+FieldResult($GET,$h,'Tax');
	$Profit = $Profit+FieldResult($GET,$h,'Tax');

	$RPSTR .= number_format(FieldResult($GET,$h,'COMM1'),2,',','.').$currency.'</td>
	<td align="right" style="padding-left:10px;padding-right:2px;border-bottom:1px solid #BBB;border-left:1px solid #CCC;">'.number_format(FieldResult($GET,$h,'COMM2'),2,',','.').$currency.'</td>
	<td align="right" style="padding-left:10px;padding-right:2px;border-bottom:1px solid #BBB;border-left:1px solid #CCC;">'.number_format(FieldResult($GET,$h,'imp'),2,',','.').$currency;
	} else {
	$RPSTR .= number_format((FieldResult($GET,$h,'Tax')),2,',','.').$currency.'</td>
	<td align="right" style="padding-left:10px;padding-right:2px;border-bottom:1px solid #BBB;border-left:1px solid #CCC;">';
	$RPSTR .= number_format((FieldResult($GET,$h,'Prom')),2,',','.').$currency;
	}
	$RPSTR .= '</td>
	<td align="right" style="padding-left:10px;padding-right:2px;border-bottom:1px solid #BBB;border-left:1px solid #777;" NOWRAP>'.number_format($ProfitAmount,2,',','.').$currency.'</td>
	<td align="right" style="padding-left:10px;padding-right:2px;border-bottom:1px solid #BBB;border-left:1px solid #CCC;" NOWRAP>'.number_format($ProfitAmount+FieldResult($GET,$h,'Tax'),2,',','.').$currency.'</td>
	<td align="right" style="padding-left:10px;padding-right:2px;border-bottom:1px solid #BBB;border-left:1px solid #CCC;" NOWRAP>'.number_format($Profit,1,',','.').' %</td>
	<td align="right" style="padding-left:10px;padding-right:2px;border-bottom:1px solid #BBB;border-left:1px solid #777;" NOWRAP>'.number_format(FieldResult($GET,$h,'Tax')/FieldResult($GET,$h,'Counter'),2,',','.').$currency.'</td>
	<td align="right" style="padding-left:10px;padding-right:2px;border-bottom:1px solid #BBB;border-left:1px solid #CCC;" NOWRAP>'.number_format((($ProfitAmount)/FieldResult($GET,$h,'Counter')),2,',','.').$currency.'</td>
	<td align="right" style="padding-left:10px;padding-right:2px;border-bottom:1px solid #BBB;border-left:1px solid #CCC;" NOWRAP>'.number_format((FieldResult($GET,$h,'OUTAmount')/FieldResult($GET,$h,'Counter')),2,',','.').$currency.'</td>
	<td align="right" style="padding-left:10px;padding-right:2px;border-bottom:1px solid #BBB;border-left:1px solid #777;" NOWRAP>'.number_format(FieldResult($GET,$h,'Counter')/FieldResult($GET,$h,'Members'),1).'</td>
	<td align="right" style="padding-left:10px;padding-right:2px;border-bottom:1px solid #BBB;border-left:1px solid #777;" NOWRAP>'.FieldResult($GET,$h,'NewClients').'</td>';
	if($_POST['Countries']==0){
	$RPSTR .= '<td align="right" style="padding-left:10px;padding-right:2px;border-bottom:1px solid #BBB;border-left:1px solid #777;" NOWRAP>'.number_format(FieldResult($GET,$h,'ValAmount'),2,',','.').'</td>
	<td align="right" style="padding-left:10px;padding-right:2px;border-bottom:1px solid #BBB;border-left:1px solid #CCC;" NOWRAP>'.number_format($ProfitAmount+FieldResult($GET,$h,'Tax')-FieldResult($GET,$h,'ValAmount'),2,',','.').'</td>';
	} else {
	$RPSTR .= '<td align="right" style="padding-left:10px;padding-right:2px;border-bottom:1px solid #BBB;border-left:1px solid #777;" NOWRAP>'.number_format(FieldResult($GET,$h,'ValAmount'),2,',','.').'</td>
	<td align="right" style="padding-left:10px;padding-right:2px;border-bottom:1px solid #BBB;border-left:1px solid #CCC;" NOWRAP>'.number_format(FieldResult($GET,$h,'OUTAmount')-FieldResult($GET,$h,'ValAmount'),2,',','.').'</td>
	<td align="right" style="padding-left:10px;padding-right:2px;border-bottom:1px solid #BBB;border-left:1px solid #CCC;" NOWRAP>'.number_format($PercentGoal,1,',','.').' %</td>';
	}
	$RPSTR .= '</tr>';
	}
}
	}
	$RPSTR .= '</table></div>';
    echo $RPSTR;
?>